using System;
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using YushengMall.Backend.Api.Entity;

namespace YushengMall.Backend.Api.Database
{
    public class DbHelper : DbContext
    {

        private readonly static string connectSql = "server=.;database=yusheng_mall;uid=sa;pwd=123456;";

        public DbHelper()
        {

        }

        public DbHelper(DbContextOptions options) : base(options)
        {

        }

        public DbSet<Role> Role { get; set; }
        public DbSet<Users> User { get; set; }
        public DbSet<SystemMenu> SystemMenu { get; set; }
        public DbSet<UserRole> UserRole { get; set; }
        public DbSet<MenuRes> MenuRes { get; set; }
        public DbSet<MenuTagType> menuTagType { get; set; }
        public DbSet<TagType> tagType { get; set; }
        public DbSet<sheet> sheet { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connectSql);
            //optionsBuilder.UseNpgsql(connectSql);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            var adminRoleID = Guid.NewGuid();
            var adminUserID = Guid.NewGuid();
            modelBuilder.Entity<Role>().HasData(
                new Role
                {
                    ID = adminRoleID,
                    RoleName = "管理员",
                    IsActived=true,
                    IsDeleted=false,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                },
                new Role
                {
                    ID = Guid.NewGuid(),
                    RoleName = "用户",
                    IsActived=true,
                    IsDeleted=false,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                }
            );
            modelBuilder.Entity<Users>().HasData(
                new Users
                {
                    ID = adminUserID,
                    UserName = "amdin",
                    PassWord = ".jxy123,",
                    RoleID = adminRoleID,
                    NickName = "系统管理员",
                    IsActived=true,
                    IsDeleted=false,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now

                }
            );

            modelBuilder.Entity<UserRole>().HasData(
                
                new UserRole
                {
                    ID=Guid.NewGuid(),
                    UserID=adminUserID,
                    RoleID = adminRoleID,
                    IsActived = true,
                    IsDeleted = false,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                }
             );

            base.OnModelCreating(modelBuilder);
        }

        public static DataTable QueryTable(string DbSql)
        {
            SqlDataAdapter sqlData = new SqlDataAdapter(DbSql, connectSql);
            DataTable data = new DataTable();
            sqlData.Fill(data);
            return data;
        }

        public static int UpdateOrDelTable(string DbSql)
        {
            SqlConnection sqlConnection = new SqlConnection(connectSql);
            SqlCommand sqlCommand = new SqlCommand(DbSql, sqlConnection);
            sqlConnection.Open();
            var res = sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
            return res;
        }

        public static int IsExistence(string Dbsql)
        {
            SqlConnection sqlConnection = new SqlConnection(connectSql);
            SqlCommand sqlCommand = new SqlCommand(Dbsql, sqlConnection);
            sqlConnection.Open();
            var res = sqlCommand.ExecuteScalar();
            sqlConnection.Close();
            return Convert.ToInt32(res);
        }

    }
}